package xupt.se.ttms.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import xupt.se.ttms.idao.iTicketDAO;
import xupt.se.ttms.model.Ticket;
import xupt.se.util.MyDBUtil;

public class TicketDAO implements iTicketDAO {
	private final String insertSql="insert into T_Ticket(mv_name,mv_time,seat_row,seat_col,play_time,studio_name,price)values(?,?,?,?,?,?,?)";
	private final String deleteSql="delete from T_Ticket where ticket_id=?";	
	private final String updateSql="update T_Ticket set mv_name=?,mv_time=?,seat_row=?,seat_col=?,play_time=?,studio_name=?,price=? where ticket_id=?";
	private final String selectSqlInt="select * from T_Ticket where ticket_id=?";
	private final String selectSqlStr="select * from T_Ticket where mv_name=?";
	private final String selectSqlAll="select * from T_Ticket ";

	/**
	 * 查询，传一个类型String,防sql注入攻击
	 * @param sql		sql语句
	 * @param str		where条件
	 * @return
	 * @throws Exception
	 */
	public ResultSet execQuery(String str) throws Exception {
		String sql=selectSqlStr;
		ResultSet rstSet = null;
		MyDBUtil myDBUtil=new MyDBUtil();
		rstSet=myDBUtil.execQuery(sql, str);
		return rstSet;
	}
	/**
	 * 查询，传一个类型int,防sql注入攻击
	 * @param sql		sql语句
	 * @param i			where条件
	 * @return
	 * @throws Exception
	 */
	public ResultSet execQuery(int i) throws Exception {
		String sql=selectSqlInt;
		ResultSet rstSet = null;
		MyDBUtil myDBUtil=new MyDBUtil();
		rstSet=myDBUtil.execQuery(sql, i);
		return rstSet;
	}	

	/**
	 * 删除 数据，传一个类型int，防sql注入攻击
	 * @param sql
	 * @param i
	 * @return
	 * @throws Exception
	 */
	public boolean execDelete(int i) throws Exception{
		String sql=deleteSql;
		boolean flag = false;
		MyDBUtil myDBUtil=new MyDBUtil();
		flag=myDBUtil.execDelete(sql, i);
		return flag;
	}
	@Override
	public ResultSet exexQueryAll() throws Exception {
		String sql=selectSqlAll;
		ResultSet rstSet = null;
		MyDBUtil myDBUtil=new MyDBUtil();
		rstSet=myDBUtil.execAllQuery(sql);
		return rstSet;
	}

	@Override
	public List<Ticket> HandleTicketResultSet(ResultSet rs) throws SQLException {
		
		 //结果，在第一个查询结果有个rs标记，通过rs.next()遍历获取全部
		Ticket ticket=null;
		List<Ticket> tickets=new ArrayList<>();
		 //获取结果的数据
        ResultSetMetaData rsmd = rs.getMetaData();
        //获取该条结果有多少个列数据
        int columnCount = rsmd.getColumnCount();
        //通过rs.next()遍历获取全部结果
       
    	System.out.println("获取票务数据中");
    	 while (rs.next()) {
    	        
     		ticket=new Ticket();
     		
       	 //输出每条结果的全部数据
           for (int i = 0; i < columnCount; i++) {
        	   	ticket.setTicket_id(rs.getInt("ticket_id"));
        	  	ticket.setMv_name(rs.getString("mv_name"));
        	  	ticket.setMv_time(rs.getString("mv_time"));
        	  	ticket.setSeat_row(rs.getInt("seat_row"));
        	  	ticket.setSeat_col(rs.getInt("seat_col"));
        	  	ticket.setPlay_time(rs.getString("play_time"));
        	  	ticket.setStudio_name(rs.getString("studio_name"));
        	  	ticket.setPrice(rs.getFloat("price"));
        
               System.out.print(rs.getString(i+1) + "\t");

           }
           tickets.add(ticket);
           System.out.println();		                    
        }
        
        return tickets;
	}
	@Override
	public boolean execInsert(Ticket ticket) throws Exception {
		String sql=insertSql;
		Connection conn=null;
		boolean flag=false;
		PreparedStatement ps=null;
		MyDBUtil db = new MyDBUtil();
		conn=db.openConnection();
		try {
			if(null==conn)
				throw new Exception("Database not connected!");
			
			ps=conn.prepareStatement(sql);
			//ps.setInt(1,ticket.getTicket_id());
			ps.setString(1,ticket.getMv_name());
			ps.setString(2,ticket.getMv_time());
			ps.setInt(3,ticket.getSeat_row());
			ps.setInt(4,ticket.getSeat_col());
			ps.setString(5, ticket.getPlay_time());
			ps.setString(6, ticket.getStudio_name());
			ps.setFloat(7, ticket.getPrice());
			flag=ps.execute();
			System.out.println(flag);
			/*
			Statement stmt = conn.createStatement();
			flag = stmt.execute(sql);*/			
			ps.close();	
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return flag;
	}
	@Override
	public boolean execUpdate(Ticket ticket) throws Exception {
		String sql=updateSql;
		Connection conn=null;
		boolean flag=false;
		PreparedStatement ps=null;
		MyDBUtil db = new MyDBUtil();
		conn=db.openConnection();
		try {
			if(null==conn)
				throw new Exception("Database not connected!");
			
			ps=conn.prepareStatement(sql);
			ps.setString(1,ticket.getMv_name());
			ps.setString(2,ticket.getMv_time());
			ps.setInt(3,ticket.getSeat_row());
			ps.setInt(4,ticket.getSeat_col());
			ps.setString(5, ticket.getPlay_time());
			ps.setString(6, ticket.getStudio_name());
			ps.setFloat(7, ticket.getPrice());
			
			
			ps.setInt(8, ticket.getTicket_id());
			flag=ps.execute();
			/*
			Statement stmt = conn.createStatement();
			flag = stmt.execute(sql);*/			
			ps.close();	
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return flag;
	}
	
}
